
package com.ljxt.dt.dao.basicinformationdao.impl;

import com.ljxt.dt.dao.basicinformationdao.RowMapper;
import com.ljxt.dt.dao.basicinformationdao.SupplierDao;
import com.ljxt.dt.domain.GoodsInfo;
import com.ljxt.dt.domain.SupplierInfo;
import com.ljxt.dt.domain.SupplierType;
import com.ljxt.dt.util.thl.ThlUtils;
import com.ljxt.dt.vo.basicInformation.GoodsInfoVO;
import com.ljxt.dt.vo.basicInformation.SupplierListVO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 糖糖
 * @version 1.0.0
 * @date 2020/5/12 16:39
 * @copyright 老九学堂
 */
public class SupplierDaoImpl implements SupplierDao {

    @Override
    public List<SupplierInfo> findAllSuppliers() {
        String sql = "select * from supplier_info";
        RowMapper<SupplierInfo> rowMapper = new RowMapper<SupplierInfo>() {
            @Override
            public SupplierInfo mapRow(ResultSet resultSet) throws Exception {
                SupplierInfo supplierInfo = new SupplierInfo();
                supplierInfo.setId(resultSet.getInt("id"));
                supplierInfo.setSupplierName(resultSet.getString("supplier_name"));
                return supplierInfo;
            }
        };
        return ThlUtils.executeQuery(sql,rowMapper);
    }

    @Override
    public List<SupplierType> selectSupplierType() {
        //定义sql
        String sql = "select * from supplier_type";
        RowMapper<SupplierType> rowMapper = new RowMapper<SupplierType>() {
            @Override
            public SupplierType mapRow(ResultSet resultSet) throws Exception {
                SupplierType supplierType = new SupplierType();
                supplierType.setId(resultSet.getInt("id"));
                supplierType.setSupplierType(resultSet.getString("supplier_type"));
                return supplierType;
            }
        };
        // return  DaoUtil.querySelectAll(sql,new SupplierType());
        return ThlUtils.executeQuery(sql, rowMapper);
    }

    @Override
    public List<SupplierListVO> findAll(Integer currentIndex, Integer pageSize, String supplierName, Integer typeId, Integer id) {

        //根据供应商查找是模糊查找，所以需要在参数两端拼接%
        supplierName = "%" + supplierName + "%";
            //定义一个变量保存sql
            String sql;
            //定义装参数的集合
            List<Object> paramsList = new ArrayList<>();
             paramsList.add(supplierName);
            //定义sql
            sql = "select s1.*, s2.supplier_type,s3.contact_name, s3.contact_phone from supplier_info as s1 inner join supplier_type as s2 on(s1.supplier_type_id = s2.id) inner join supplier_contact as s3 on(s1.id = s3.supplier_id) where supplier_name like ? ";
            //判断类型id不为0，则拼接sql的条件进去，并向集合添加参数
            if(typeId != 0){
                sql += " and s1.supplier_type_id = ? ";
                paramsList.add(typeId);
            }
            //判断传入的供应商id的参数不为-1，则拼接参数条件进去，并向集合添加参数
            if(id != -1){
                sql += " and s1.id = ? ";
                paramsList.add(id);
            }
            //最后拼接分页参数，并向集合添加参数
            sql += " limit ?,? ";
            paramsList.add((currentIndex -1) * pageSize);
            paramsList.add(pageSize);
            //最后将集合转换成数组
            Object[] params = paramsList.toArray();

        //构建RowMapper
        RowMapper<SupplierListVO> rowMapper = new RowMapper<SupplierListVO>() {
            @Override
            public SupplierListVO mapRow(ResultSet resultSet) throws Exception {
                SupplierListVO supplierListVO = new SupplierListVO();
                    supplierListVO.setId(resultSet.getInt("id"));
                    supplierListVO.setAddress(resultSet.getString("address"));
                    supplierListVO.setContactName(resultSet.getString("contact_name"));
                    supplierListVO.setPhone(resultSet.getString("contact_phone"));
                    supplierListVO.setSupplierName(resultSet.getString("supplier_name"));
                    supplierListVO.setSupplierTypeId(resultSet.getInt("supplier_type_id"));
                    supplierListVO.setSupplierType(resultSet.getString("supplier_type"));
                return supplierListVO;
            }
        };

        return ThlUtils.executeQuery(sql, rowMapper,params);
    }

    @Override
    public int selectCount(int currentIndex, int pageSize, String supplierName, int typeId, int id) {
        //根据供应商查找是模糊查找，所以需要在参数两端拼接%
        supplierName = "%" + supplierName + "%";
        //定义一个变量保存sql
        String sql;
        //定义装参数的集合
        List<Object> paramsList = new ArrayList<>();
        paramsList.add(supplierName);
        //定义sql
        sql = "select count(*) from supplier_info as s1 inner join supplier_type as s2 on(s1.supplier_type_id = s2.id) inner join supplier_contact as s3 on(s1.id = s3.id) where supplier_name like ? ";
        //判断类型id不为0，则拼接sql的条件进去，并向集合添加参数
        if(typeId != 0){
            sql += " and s1.supplier_type_id = ? ";
            paramsList.add(typeId);
        }
        //判断传入的供应商id的参数不为-1，则拼接参数条件进去，并向集合添加参数
        if(id != -1){
            sql += " and s1.id = ? ";
            paramsList.add(id);
        }

        //最后将集合转换成数组
        Object[] params = paramsList.toArray();
        //执行方法
        int count = Integer.parseInt(ThlUtils.executeQueryUnique(sql,params).toString());
        return count;
    }

    @Override
    public List<GoodsInfoVO> findAllGoods(int id, String goodsName, int currentIndex, int pageSize) {
        //定义一个集合用来保存要传入的参数
        List<Object> paramsList = new ArrayList<>();

        //拼接模糊查询需要的字符串,并添加到集合中
        goodsName = "%" + goodsName + "%";
        paramsList.add(goodsName);

        //定义sql语句
        String sql = "select * from goods_info where goods_name like ?";
        //判断id的值，再拼接字符串
        if(id != -1){
            sql += " and id = ?";
            paramsList.add(id);
        }
        sql += " limit ?,? ";
        paramsList.add((currentIndex -1) * pageSize);
        paramsList.add(pageSize);
        //将参数集合转换为数组
        Object[] params = paramsList.toArray();

        //构建RowMapper
        RowMapper<GoodsInfoVO> rowMapper = new RowMapper<GoodsInfoVO>() {
            @Override
            public GoodsInfoVO mapRow(ResultSet resultSet) throws Exception {
                GoodsInfoVO goodsInfoVO = new GoodsInfoVO();
                goodsInfoVO.setId(resultSet.getInt("id"));
                goodsInfoVO.setGoodsName(resultSet.getString("goods_name"));
                goodsInfoVO.setUnit(resultSet.getString("unit"));
                goodsInfoVO.setBrand(resultSet.getString("brand"));
                //将分类属性以,分割成数组
                String category = resultSet.getString("category");
                //以 ，拆分字符串成一个字符串数组
                String[] strs = category.split(",");
                //根据数据长度生成不同的sql语句
                String sql1= "";
                if(strs.length == 1){
                    sql1 = "select first_name from first_type where id = " + strs[0];
                }
                if(strs.length == 2){
                    sql1 = "select second_name from second_type where id = " + strs[1];
                }
                if(strs.length == 3){
                    sql1 = "select third_name from third_type where id = " + strs[2];
                }

                //调用方法查询一个字段
                String str = (String) ThlUtils.executeQueryUnique(sql1);
                goodsInfoVO.setCategoryName(str);

                goodsInfoVO.setCategory(resultSet.getString("category"));
                goodsInfoVO.setPicture(resultSet.getString("picture"));
                return goodsInfoVO;
            }
        };

        return ThlUtils.executeQuery(sql, rowMapper, params);
    }


    @Override
    public int findAllGoodsCount(int id, String goodsName, int currentIndex, int pageSize) {
        //定义一个集合用来保存要传入的参数
        List<Object> paramsList = new ArrayList<>();

        //拼接模糊查询需要的字符串,并添加到集合中
        goodsName = "%" + goodsName + "%";
        paramsList.add(goodsName);

        //定义sql语句
        String sql = "select count(*) from goods_info where goods_name like ?";
        //判断id的值，再拼接字符串
        if(id != -1){
            sql += " and id = ?";
            paramsList.add(id);
        }
        //将参数集合转换为数组
        Object[] params = paramsList.toArray();

        //执行方法查询总数
        int total = Integer.parseInt(ThlUtils.executeQueryUnique(sql,params).toString());

        return total;
    }

    @Override
    public boolean addNewSupplier(int typeId, String typeName, String address, String contactName, String phone, String[] goodsIdArray) {

        //给供应商信息表插入新的供应商的sql
        String sql = "insert into supplier_info values(null,?,?,?)";
        Object[] params = {typeName, typeId, address};
        //调用方法执行插入供应商
        int row1 = ThlUtils.executeUpdate(sql, params);


        //再查找新增的供应商编号
        String sql1 = "select id from supplier_info order by id desc limit 1";
        //调用方法查询刚插入的供应商id值
        Object id = ThlUtils.executeQueryUnique(sql1);

        //给联系人表插入新的联系人的sql
        String sql2 = "insert into supplier_contact values(null,?,?,?)";
        Object[] params1 = {Integer.parseInt(id.toString()), contactName, phone};
        //调用方法执行插入联系人
        int row2 = ThlUtils.executeUpdate(sql2, params1);

        if(goodsIdArray != null && goodsIdArray.length != 0){
            for(int i = 0; i < goodsIdArray.length; i++ ){
                //给供应商信息和商品联系表插入商品的sql
                String sql3 = "insert into supplier_goods values(null,?,?)";
                Object[] params3 = {goodsIdArray[i],Integer.parseInt(id.toString())};
                //调用方法插入信息
                int row = ThlUtils.executeUpdate(sql3, params3);
            }
        }

        return true;
    }

    @Override
    public List<GoodsInfoVO> findSupplierGoods(int id) {

        String sql = "select s.goods_id,s.supplier_id, g.* from supplier_goods as s inner JOIN goods_info as g on(s.goods_id = g.id) where s.supplier_id = ?";
        Object[] params = {id};
        //定义一个map，用来保存商品的不同属性值
        Map<String,String> attrMap = new HashMap<>();
        RowMapper<GoodsInfoVO> rowMapper = new RowMapper<GoodsInfoVO>() {
            @Override
            public GoodsInfoVO mapRow(ResultSet resultSet) throws Exception {
                GoodsInfoVO goodsInfoVO = new GoodsInfoVO();
                goodsInfoVO.setId(resultSet.getInt("id"));
                goodsInfoVO.setGoodsName(resultSet.getString("goods_name"));
                goodsInfoVO.setUnit(resultSet.getString("unit"));
                //查询对应商品的有多少个属性，并将属性放在map里面
                String sql1 = "select  temp.category_name as name,GROUP_CONCAT(temp.attr_name) as attr from (select c.category_name, a.attr_name from category_info as c inner join attr_info as a on(c.id = a.category_id) where c.goods_id = ?) as temp GROUP BY temp.category_name";

                //获得connection连接
                Connection connection = ThlUtils.getConnection();
                //Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/dt", "root", "123456");
                //获得执行sql语句的PreparedStatement对象
                PreparedStatement preparedStatement = ThlUtils.getPreparedStatement(connection, sql1, params);

                //执行查询,返回结果集对象
                ResultSet resultSet1 = preparedStatement.executeQuery();
                //遍历结果集，将表中的每一行数据（只有两列）保存成键值对的形式，存在map中
                while(resultSet1.next()){
                    attrMap.put(resultSet1.getString(1),resultSet1.getString(2));
                }
                connection.close();

                goodsInfoVO.setAttrMap(attrMap);
                return goodsInfoVO;
            }
        };
        return ThlUtils.executeQuery(sql, rowMapper, params);
    }


    @Override
    public boolean updateSupplier(int id,int typeId, String supplierName, String address, String contactName, String phone, String[] goodsIdArray) {
        //更新供应商表的信息
        String sql1 = "update supplier_info set supplier_name = ?, supplier_type_id = ?, address = ? where id = ?";
        Object[] params1 = {supplierName, typeId, address,id};
        ThlUtils.executeUpdate(sql1,params1);

        //更新联系人表信息
        String sql2 = "update supplier_contact set contact_name = ?, contact_phone = ? where supplier_id = ?";
        Object[] params2 = {contactName, phone, id};
        ThlUtils.executeUpdate(sql2,params2);

        //1.更新供应商和商品联系表信息
        //1.1 更新前先删除原来的对应的商品信息
        String sql3 = "delete from supplier_goods where supplier_id = ? ";
        Object[] params3 = {id};
        ThlUtils.executeUpdate(sql3, params3);

        if(goodsIdArray != null && goodsIdArray.length != 0){
            //1.2 再执行增加
            String sql4 = "insert into supplier_goods values(null, ?,?)";
            //1.3将数组中的值循环赋值到参数数组
            for(int i = 0; i < goodsIdArray.length; i++){
                Object[] params4 = {goodsIdArray[i],id};
                ThlUtils.executeUpdate(sql4,params4);
            }
       }
        return true;
    }
}
